package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class MenuItemDAO {
    public void addMenuItem(MenuItem item) throws Exception {
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "INSERT INTO MenuItems (StoreID, ItemName, ItemType, Price) VALUES (?, ?, ?, ?)";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setInt(1, item.getStoreID());
            statement.setString(2, item.getItemName());
            statement.setString(3, item.getItemType());
            statement.setDouble(4, item.getPrice());
            statement.executeUpdate();
        }
    }
    //向数据库中的MenuItems表添加一个新的菜单项
    public void deleteMenuItem(int itemID) throws Exception {
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "DELETE FROM MenuItems WHERE ItemID = ?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setInt(1, itemID);
            statement.executeUpdate();
        }
    }//防止注入攻击
    public List<MenuItem> getMenuItems() throws Exception {
        List<MenuItem> items = new ArrayList<>();
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "SELECT * FROM MenuItems";
            PreparedStatement statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                items.add(new MenuItem(
                        resultSet.getInt("ItemID"),
                        resultSet.getInt("StoreID"),
                        resultSet.getString("ItemName"),
                        resultSet.getString("ItemType"),
                        resultSet.getDouble("Price")
                ));
            }
        }
        return items;
    }//从数据库中获取菜单项
    public List<MenuItem> getMenuItemsByStoreID(int storeID) throws Exception {
        List<MenuItem> items = new ArrayList<>();
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "SELECT * FROM MenuItems WHERE StoreID = ?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setInt(1, storeID);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                items.add(new MenuItem(
                        resultSet.getInt("ItemID"),
                        resultSet.getInt("StoreID"),
                        resultSet.getString("ItemName"),
                        resultSet.getString("ItemType"),
                        resultSet.getDouble("Price")
                ));
            }
        }
        return items;
    }//根据给定的商店ID（storeID）从数据库中查询并返回
    public List<MenuItem> getMenuItemsByTypeAndStore(int storeID, String itemType) throws Exception {
        List<MenuItem> items = new ArrayList<>();
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "SELECT * FROM MenuItems WHERE StoreID = ? AND ItemType = ?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setInt(1, storeID);
            statement.setString(2, itemType);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                items.add(new MenuItem(
                        resultSet.getInt("ItemID"),
                        resultSet.getInt("StoreID"),
                        resultSet.getString("ItemName"),
                        resultSet.getString("ItemType"),
                        resultSet.getDouble("Price")
                ));
            }
        }
        return items;
    }//根据商店ID和菜单项类型从数据库中查询并返回
    public void updatePrice(int itemID, double newPrice) throws Exception {
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "UPDATE MenuItems SET Price = ? WHERE ItemID = ?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setDouble(1, newPrice);
            statement.setInt(2, itemID);
            statement.executeUpdate();
        }
    }
}//更新数据库中某个菜单项的价格
